import logging
import sys
import os
sys.path.append(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
from qmt_tool.doris_tool import DbTool
from qmt_tool.redis_cache import RedisQueue
from qmt_data import jsl_data
import  akshare as  ak
import  time
import pandas as pd
from sqlalchemy import text
from qmt_tool.MyTT import REF,SMA,EMA,BARSLAST,LLV,HHV
from qmt_tool.confile_tool import get_config_jsl_file
from datetime import  datetime,timedelta
import pendulum
import  json
import hashlib
from qmt_data.tdxmoo_data import tdxmoo_data
from qmt_data.stock_index_data import stock_index_data
class Download2DB:
    def __init__(self):
        self.db_tool = DbTool()
        self.engine = self.db_tool.get_dbengine_quant()
        self.engine_result = self.db_tool.get_dbengine_quant_result()
        # self.logcmd = get_logger(name='downdata2db')
        self.redis= RedisQueue()

    def down_trade_cal(self):
        """
        下载交易日历,只要年底一次，或者一个月1次就行
        :rtype: None
        :return:
        """
        dbengine = self.engine

        tool_trade_date_hist_sina_df = ak.tool_trade_date_hist_sina()
        table_name = "trade_cal_ak_source"
        tool_trade_date_hist_sina_df['is_open'] = 1
        trade_date = tool_trade_date_hist_sina_df['trade_date'].values
        tool_trade_date_hist_sina_df['pretrade_date'] = REF(trade_date, 1)
        tool_trade_date_hist_sina_df['nexttrade_date'] = REF(trade_date, -1)


        self.db_tool.save_to_mysql_append(tool_trade_date_hist_sina_df, dbengine, table_name)

    def down_stock_list_ak_todb(self):

            date_time = time.strftime("%Y-%m-%d", time.localtime())

            sh_stock_df = pd.DataFrame()
            stock_info_sh_name_code_df = ak.stock_info_sh_name_code(symbol="主板A股")  # 获取 上交所股票列表
            sh_stock_df['code'] = stock_info_sh_name_code_df["证券代码"]  # 只要有用的信息，代码和简称
            sh_stock_df['name'] = stock_info_sh_name_code_df["证券简称"]
            sh_stock_df['exchange'] = "SH"  # 交易所标志

            kc_stock_df = pd.DataFrame()
            stock_info_kc_name_code_df = ak.stock_info_sh_name_code(symbol="科创板")  # 获取 上交所股票列表
            kc_stock_df['code'] = stock_info_kc_name_code_df["证券代码"]  # 只要有用的信息，代码和简称
            kc_stock_df['name'] = stock_info_kc_name_code_df["证券简称"]
            kc_stock_df['exchange'] = "SH"  # 交易所标志

            sz_stock_df = pd.DataFrame()
            stock_info_sz_name_code_df = ak.stock_info_sz_name_code()  # 获取深交所 股票列表
            sz_stock_df['code'] = stock_info_sz_name_code_df["A股代码"]  # 只要有用的信息，代码和简称
            sz_stock_df['name'] = stock_info_sz_name_code_df["A股简称"]
            sz_stock_df['exchange'] = "SZ"

            # bj_stock_df = pd.DataFrame()
            # stock_info_bj_name_code_df = ak.stock_info_bj_name_code()     # 获取北交所 股票列表
            # bj_stock_df['code'] = stock_info_bj_name_code_df["证券代码"]
            # bj_stock_df['name'] = stock_info_bj_name_code_df["证券简称"]
            # bj_stock_df['exchange'] = "bj"

            # df_all_stock = pd.concat([sh_stock_df, sz_stock_df,kc_stock_df, bj_stock_df], keys=['code', 'name', 'exchange'])

            df_all_stock = pd.concat([sh_stock_df, sz_stock_df, kc_stock_df], keys=['code', 'name', 'exchange'])
            # print(df_all_stock)
            stock_base_info_list = []
            for row in df_all_stock.itertuples():  # 对每一个股票进行更多信息的获取，
                # time.sleep(0.02)
                try:
                    stock = str(getattr(row, 'code')).rjust(6, '0')
                    print(stock)
                    # stock='689009'
                    stock_individual_info_em_df = ak.stock_individual_info_em(symbol=stock)
                    # stock_em_dict = stock_individual_info_em_df.to_dict()
                    # stock_individual_info_em_df = stock_individual_info_em_df.set_index('item')
                    stock_base_info_list.append(
                        [
                            stock_individual_info_em_df[stock_individual_info_em_df['item'] == '股票代码'][
                                'value'].values[0],
                            stock_individual_info_em_df[stock_individual_info_em_df['item'] == '股票简称'][
                                'value'].values[0],
                            stock_individual_info_em_df[stock_individual_info_em_df['item'] == '上市时间'][
                                'value'].values[0],
                            stock_individual_info_em_df[stock_individual_info_em_df['item'] == '行业'][
                                'value'].values[0],
                            stock_individual_info_em_df[stock_individual_info_em_df['item'] == '总股本'][
                                'value'].values[0],
                            stock_individual_info_em_df[stock_individual_info_em_df['item'] == '流通股'][
                                'value'].values[0],
                            stock_individual_info_em_df[stock_individual_info_em_df['item'] == '总市值'][
                                'value'].values[0],
                            stock_individual_info_em_df[stock_individual_info_em_df['item'] == '流通市值'][
                                'value'].values[0],

                         getattr(row, 'exchange')
                         ]
                    )
                except Exception as e:
                    print('这个股没取到', row, e)
            stock_base_info_df = pd.DataFrame(stock_base_info_list,
                                              columns=(
                                              'symbol', 'name', 'list_date', 'industry', 'total_share', 'float_share',
                                              'total_value'
                                              , 'float_value', 'market'))

            # save into db
            stock_base_info_df['stock_code'] = stock_base_info_df['symbol'] + "." + stock_base_info_df['market']
            table_name = 'ak_stock_list'
            # 预定义表列类型，可以在代码中预定义， 会按照表定义，自行创建这个表结构
            try:
                self.db_tool.save_to_mysql_replace(df=stock_base_info_df, engine=self.engine, table_name=table_name)
                print('股票list更新完毕')
            except Exception as e:
                print('报错啦', e)

            return "success"

    def down_stock_daily_qfq_todb(self,start_date='20240101', end_date= datetime.now().strftime('%Y%m%d')):

        stock_list_df = self.db_tool.get_alldata_fromdb(self.engine, "ak_stock_list")
        kline_qfq_tbl_name = "ak_stock_daily_qfq_em"  # 前复权 表名
        start_date = start_date
        end_date = end_date
        # 获取每只票的前复权数据
        for stock_info in stock_list_df.itertuples():
            # 获取单只票的日线数据
            # print("正在获取", stock_info.symbol)
            try:
                stock_zh_a_hist_df = ak.stock_zh_a_hist(symbol=stock_info.symbol, period="daily", start_date=start_date,
                                                        end_date=end_date, adjust='qfq')
                # time.sleep(0.01)
            except Exception as e:
                print("Got error: %s" % (e))
                time.sleep(1)
                try:
                    stock_zh_a_hist_df = ak.stock_zh_a_hist(symbol=stock_info.symbol, period="daily",
                                                            start_date=start_date, end_date=end_date, adjust='qfq')
                except Exception as e:
                    continue
            # 对单只票的数据进行格式化为 英文
            # stock_zh_a_hist_df['symbol'] = stock_info.symbol
            stock_zh_a_hist_df['name'] = stock_info.name
            stock_zh_a_hist_df.rename(
                columns={'日期': 'trade_date',"股票代码":'symbol', '开盘': 'open', '收盘': 'close', '最高': 'high', '最低': 'low',
                         '成交量': 'volume',
                         '成交额': 'amount', '振幅': 'amplitude', '涨跌幅': 'zhangdiefu', '涨跌额': 'zhangdie_amount',
                         '换手率': 'turnover_rate'}, inplace=True)
            stock_zh_a_hist_df.dropna(subset=["close"], how='any', inplace=True)
            # 这里每次循环，就会写一次股票数据
            print('已经获取当前票'+str(stock_info[1]))
            self.db_tool.save_to_mysql_append(stock_zh_a_hist_df, self.engine, kline_qfq_tbl_name)
        return "success"

    def download_index_data(self,start_date='20240101', end_date= datetime.now().strftime('%Y%m%d')):
        #下载指数数据的日线行情

        pass
    def download_zhangdiejiashu(self):
        moo=tdxmoo_data()
        df=moo.get_zhangdiejiashu()
        self.db_tool.save_to_mysql_append(df, self.engine, 'tdx_zhangdiejiashu')

    def download_zuorizhangting(self):
        moo=tdxmoo_data()
        df=moo.get_zuorizhangting()
        self.db_tool.save_to_mysql_append(df, self.engine, 'tdx_zuorizhangting')
    def download_index_jhjj_data(self,symbol="399001",symbol_name="深证指数", end_date= datetime.now().strftime('%Y%m%d')):
        #下载指数的集合竞价行情，只能下载最近1周，因此需要每天都跑
        Cindex = stock_index_data()
        start_date = (datetime.now() - timedelta(days=7)).strftime('%Y%m%d')
        current_date = datetime.strptime(start_date, '%Y%m%d')
        end_date_obj = datetime.strptime(end_date, '%Y%m%d')
        while current_date <= end_date_obj:
            date_str = current_date.strftime('%Y%m%d')
            try:
                # 调用 index_jhjj_data 方法并传入日期
                df = Cindex.index_jhjj_data(symbol=symbol, data_dt=date_str)

                if not df.empty:

                    to_save_db=df.rename(columns={
                        '开盘': 'open',
                        '收盘': 'close',
                        '最高': 'high',
                        '最低': 'low',
                        '成交量': 'volume',
                        '成交额': 'amount',
                        '时间': 'data_time',
                    }).drop(columns=['最新价'])
                    to_save_db["name"]=symbol_name

                    self.db_tool.save_to_mysql_append(to_save_db, self.engine, 'ak_index_jhjj_snapshot')
            except Exception as e:
                print(f"下载 {date_str} 的指数集合竞价数据时出错: {e}")
            current_date += timedelta(days=1)




    def download_etf_daily_data(self,start_date='20240101', end_date= datetime.now().strftime('%Y%m%d')):
        #下载指数数据的日线行情
        pass

    def down_finance_tdx(self):
        pass

    def merge_spot_2daily_qfq_todb(self):
        # if check_is_trade_time():
            today = datetime.now().strftime('%Y%m%d')
            stock_zh_a_spot_em = ak.stock_zh_a_spot_em()
            trade_date = today
            stock_spot_to_daily_tmp = stock_zh_a_spot_em[
                ['代码', '名称', '最新价', '涨跌幅', '涨跌额', '成交量', '振幅', '最高', '最低', '今开', '换手率',
                 '成交额']]
            stock_spot_to_daily_tmp2=stock_spot_to_daily_tmp[stock_spot_to_daily_tmp['最新价']>0]

            stock_spot_to_merge = stock_spot_to_daily_tmp2.rename(
                columns={'代码': 'symbol', '名称': 'name', '今开': 'open', '最新价': 'close', '最高': 'high',
                         '最低': 'low', '成交量': 'volume',
                         '成交额': 'amount', '振幅': 'amplitude', '涨跌幅': 'zhangdiefu', '涨跌额': 'zhangdie_amount',
                         '换手率': 'turnover_rate'})
            stock_spot_to_merge['trade_date'] = trade_date
            stock_spot_to_merge['etl_time'] = datetime.now().strftime('%Y-%m-%d %H:%M:%S')

            self.db_tool.save_to_mysql_append(stock_spot_to_merge, self.engine, 'ak_stock_daily_qfq_em')

    def download_jihejingjia_snapshot(self):
        #个股 集合竞价的快照数据，
        today = datetime.now().strftime('%Y-%m-%d')

        # now=datetime.now()
        now_time = time.localtime()
        now = (now_time.tm_hour, now_time.tm_min, now_time.tm_sec)
        if (9, 25, 0) <= now <= (9, 30, 0) :
        # if True:
            stock_zh_a_spot_em = ak.stock_zh_a_spot_em()
            trade_date = today
            stock_spot_to_daily_tmp = stock_zh_a_spot_em[
                ['代码', '名称', '最新价', '涨跌幅', '涨跌额', '成交量', '振幅', '最高', '最低', '今开', '换手率',
                 '成交额','昨收','流通市值','量比','市盈率-动态']]
            stock_spot_to_daily_tmp.dropna(subset=["最新价"], how='any', inplace=True)

            stock_spot_to_merge = stock_spot_to_daily_tmp.rename(
                columns={'代码': 'symbol', '名称': 'name', '今开': 'open', '最新价': 'close', '最高': 'high',
                         '最低': 'low', '成交量': 'volume',
                         '成交额': 'amount', '振幅': 'amplitude', '涨跌幅': 'zhangdiefu', '涨跌额': 'zhangdie_amount',
                         '换手率': 'turnover_rate','昨收':'pre_close','流通市值':'float_value','量比':'v_ratio','市盈率-动态':'pe_ttm'})
            stock_spot_to_merge['trade_date'] = trade_date
            stock_spot_to_merge['etl_time'] = datetime.now().strftime('%Y-%m-%d %H:%M:%S')

            self.db_tool.save_to_mysql_append(stock_spot_to_merge, self.engine, 'ak_stock_jhjj_snapshot')

    def download_jihejingjia_snapshot920(self):
        today = datetime.now().strftime('%Y-%m-%d')

        # now=datetime.now()
        now_time = time.localtime()
        now = (now_time.tm_hour, now_time.tm_min, now_time.tm_sec)
        if (9, 20, 0) <= now <= (9, 25, 0):
            # if True:
            stock_zh_a_spot_em = ak.stock_zh_a_spot_em()
            trade_date = today
            stock_spot_to_daily_tmp = stock_zh_a_spot_em[
                ['代码', '名称', '最新价', '涨跌幅', '涨跌额', '成交量', '振幅', '最高', '最低', '今开', '换手率',
                 '成交额', '昨收', '流通市值', '量比', '市盈率-动态']]
            stock_spot_to_daily_tmp.dropna(subset=["最新价"], how='any', inplace=True)

            stock_spot_to_merge = stock_spot_to_daily_tmp.rename(
                columns={'代码': 'symbol', '名称': 'name', '今开': 'open', '最新价': 'close', '最高': 'high',
                         '最低': 'low', '成交量': 'volume',
                         '成交额': 'amount', '振幅': 'amplitude', '涨跌幅': 'zhangdiefu', '涨跌额': 'zhangdie_amount',
                         '换手率': 'turnover_rate', '昨收': 'pre_close', '流通市值': 'float_value', '量比': 'v_ratio',
                         '市盈率-动态': 'pe_ttm'})
            stock_spot_to_merge['trade_date'] = trade_date
            stock_spot_to_merge['etl_time'] = datetime.now().strftime('%Y-%m-%d %H:%M:%S')

            self.db_tool.save_to_mysql_append(stock_spot_to_merge, self.engine, 'ak_stock_jhjj_snapshot920')

    def download_jihejingjia_tdx925(self,start_date=datetime.now().strftime('%Y%m%d')):
        tdx_data = tdxmoo_data()
        df = tdx_data.get_jhjj_all_stock(start_date=start_date, end_date=datetime.now().strftime('%Y%m%d'))

        self.db_tool.save_to_mysql_append(df, self.engine, 'tdx_stock_jhjj_925')

    def download_first_minute_data_tdx931(self,start_date=datetime.now().strftime('%Y%m%d')):
        tdx_data = tdxmoo_data()
        df = tdx_data.get_first_minute_all_stock(start_date=start_date,
                                         end_date=datetime.now().strftime('%Y%m%d'))

        self.db_tool.save_to_mysql_append(df, self.engine, 'tdx_stock_fenbi931')

    def download_kezhuanzhai_jihejingjia_jsl(self):
        print('获取可转债全部数据')

        text = get_config_jsl_file()
        user = text['集思录账户']
        password = text['集思录密码']
        df = jsl_data.get_all_cov_bond_data(jsl_user=user, jsl_password=password)
        # print(df)
        df = df[~(df['可转债名称'].str.contains('退'))]
        df = df[~df['正股名称'].str.contains('ST')]

        df['data_dt']=datetime.now().strftime('%Y%m%d')
        df['etl_time'] = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        df.drop(
            ['index', '申万', 'btype', 't_flag', 'owned', 'hold', '纯债价值', 'put_ytm_rt', 'debt_rate', 'putting',
             'blocked', 'icons', 'sqflag', 'pb_flag', 'qstatus','is_min_price','正股年化波动率', 'option_tip','信用',
             'adj_cnt','adj_scnt','adjusted','noted','notes','bond_py','stock_py','正股PB','期权价值','市场','机构持仓',
             '到期税前收益','回售触发价','convert_price_valid','convert_price_tips','convert_cd_tip','ref_yield_info',
             'after_next_put_dt'], axis=1, inplace=True)

        self.db_tool.save_to_mysql_append(df, self.engine, 'kzz_jsl_jhjj')

        return df
        #集合竞价时间集思录的数据

    def download_kezhuanzhai_shoupan_jsl(self):
        print('获取可转债全部数据')

        text = get_config_jsl_file()
        user = text['集思录账户']
        password = text['集思录密码']
        df = jsl_data.get_all_cov_bond_data(jsl_user=user, jsl_password=password)
        # print(df)
        df = df[~(df['可转债名称'].str.contains('退'))]
        df = df[~df['正股名称'].str.contains('ST')]

        df['data_dt'] = datetime.now().strftime('%Y%m%d')
        df['etl_time'] = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        df.drop(
            ['index', '申万', 'btype', 't_flag', 'owned', 'hold', '纯债价值', 'put_ytm_rt', 'debt_rate', 'putting',
             'blocked', 'icons', 'sqflag', 'pb_flag', 'qstatus', 'is_min_price', '正股年化波动率', 'option_tip', '信用',
             'adj_cnt', 'adj_scnt', 'adjusted', 'noted', 'notes', 'bond_py', 'stock_py', '正股PB', '期权价值', '市场',
             '机构持仓',
             '到期税前收益', '回售触发价', 'convert_price_valid', 'convert_price_tips', 'convert_cd_tip',
             'ref_yield_info',
             'after_next_put_dt'], axis=1, inplace=True)

        self.db_tool.save_to_mysql_append(df, self.engine, 'kzz_jsl_dayend')

        return df
    def get_previous_quarter_end_date(self):
        current_date = pendulum.now()
        last_quarter_end_date =(current_date - pd.DateOffset(months=3)).to_period('Q').end_time
        # previous_quarter_end_date = current_date.subtract(months=3).end_of('quarter')
        return last_quarter_end_date
    def download_top10_liutonggudong(self):
        # 取10大流通股东，是为了计算自由流通股, 8月跑，10月跑，12月跑，
        # start_date = pendulum.date(2024, 6, 30)
        start_date = self.get_previous_quarter_end_date()
        current_date = start_date
        end_date = datetime.today()
        yestorday=pendulum.yesterday()
        gudong_df=pd.DataFrame()

        jisuanri = current_date.strftime('%Y%m%d')
        stock_gdfx_free_holding_detail_em_df = ak.stock_gdfx_free_holding_detail_em(date=jisuanri)
        stock_top10_liutonggudong_tmp = stock_gdfx_free_holding_detail_em_df[
            ['股票代码', '股票简称', '股东名称', '股东类型', '报告期', '期末持股-数量', '期末持股-数量变化',
             '期末持股-数量变化比例', '期末持股-持股变动', '公告日']]
        stock_top10_tosavedb = stock_top10_liutonggudong_tmp.rename(
            columns={'股票代码': 'symbol', '股票简称': 'name', '股东名称': 'gudong_name', '股东类型': 'gudong_type',
                     '报告期': 'report_term',
                     '期末持股-数量': 'hold_share', '期末持股-数量变化': 'hold_share_chg',
                     '期末持股-数量变化比例': 'hold_share_chg_ra',
                     '期末持股-持股变动': 'change_side', '公告日': 'public_date'})
        stock_top10_tosavedb['gudong_hash'] = pd.util.hash_array(stock_top10_tosavedb['gudong_name'].values).astype(str)
        stock_top10_tosavedb['gudong_hash'] = stock_top10_tosavedb['gudong_hash'].astype(str)
        # pd.concat([gudong_df,stock_gdfx_free_holding_detail_em_df],ignore_index=True)

        # current_date += timedelta(days=1)

        self.db_tool.save_to_mysql_append(stock_top10_tosavedb, self.engine, 'ak_stock_top10freegudong')

    def download_zhangtingban(self,data_dt=datetime.now().strftime('%Y%m%d')):
        # 下载涨停板


        # data_dt='20241024'
        stock_zt_pool_em_df = ak.stock_zt_pool_em(date=data_dt)

        data_todb = stock_zt_pool_em_df[['代码', '名称', '涨跌幅', '最新价', '成交额', '流通市值', '总市值', '换手率',
                                         '封板资金', '首次封板时间', '最后封板时间', '炸板次数', '涨停统计', '连板数',
                                         '所属行业']]

        data_todb['first_fengban_time'] = data_dt + " " + data_todb['首次封板时间']
        data_todb['last_fengban_time'] = data_dt + " " + data_todb['最后封板时间']
        data_todb2 = data_todb.rename(
            columns={'代码': 'symbol', '名称': 'name', '涨跌幅': 'zhangdiefu', '最新价': 'close',
                     '成交额': 'amount', '流通市值': 'float_value', '总市值': 'total_value',
                     '换手率': 'turnover_rate', '封板资金': 'fengban_amount',

                     '炸板次数': 'zhabannum', '涨停统计': 'zhangting_static',
                     '连板数': 'lianbannum', '所属行业': 'industry'})

        data_todb2.drop(columns={'首次封板时间', '最后封板时间'}, inplace=True)
        data_todb2['first_fengban_time'] = pd.to_datetime(data_todb['first_fengban_time'], format='%Y%m%d %H%M%S')
        data_todb2['last_fengban_time'] = pd.to_datetime(data_todb['last_fengban_time'], format='%Y%m%d %H%M%S')

        data_todb2['data_dt'] = data_dt
        table_name = 'ak_daily_zhangting_zhangting_em'
        table_his = 'ak_daily_zhangting_zhangting_em_his'

        self.db_tool.save_to_mysql_append(data_todb2, self.engine, table_his)
        # self.db_tool.save_to_mysql_replace(data_todb2, self.engine, table_name)
        # self.redis.write_pd_2redis(table_his, data_todb2)

    def download_jingjia_zhangtingban(self):
        data_dt = time.strftime("%Y%m%d", time.localtime())
        # data_dt='20241024'
        stock_zt_pool_em_df = ak.stock_zt_pool_em(date=data_dt)

        data_todb = stock_zt_pool_em_df[['代码', '名称', '涨跌幅', '最新价', '成交额', '流通市值', '总市值', '换手率',
                                         '封板资金', '首次封板时间', '最后封板时间', '炸板次数', '涨停统计', '连板数',
                                         '所属行业']]

        data_todb['first_fengban_time'] = data_dt + " " + data_todb['首次封板时间']
        data_todb['last_fengban_time'] = data_dt + " " + data_todb['最后封板时间']
        data_todb2 = data_todb.rename(
            columns={'代码': 'symbol', '名称': 'name', '涨跌幅': 'zhangdiefu', '最新价': 'close',
                     '成交额': 'amount', '流通市值': 'float_value', '总市值': 'total_value',
                     '换手率': 'turnover_rate', '封板资金': 'fengban_amount',

                     '炸板次数': 'zhabannum', '涨停统计': 'zhangting_static',
                     '连板数': 'lianbannum', '所属行业': 'industry'})

        data_todb2.drop(columns={'首次封板时间', '最后封板时间'}, inplace=True)
        data_todb2['first_fengban_time'] = pd.to_datetime(data_todb['first_fengban_time'], format='%Y%m%d %H%M%S')
        data_todb2['last_fengban_time'] = pd.to_datetime(data_todb['last_fengban_time'], format='%Y%m%d %H%M%S')

        data_todb2['data_dt'] = data_dt
        data_todb2['etl_time'] = datetime.now()
        table_his = 'ak_daily_jingjia_zhangting_em_his'

        self.db_tool.save_to_mysql_append(data_todb2, self.engine, table_his)


if __name__ == '__main__':
    download2db = Download2DB()
    download2db.download_zuorizhangting()

    # download2db.down_stock_list_ak_todb()
    # download2db.download_top10_liutonggudong()
    # download2db.down_stock_daily_qfq_todb()
    # download2db.download_my_index_fund_daily()
    # download2db.merge_spot_2daily_qfq_todb()
    # download2db.download_jihejingjia_snapshot()
    # download2db.download_my_index_fund_daily()